# -*- coding: utf-8 -*-
"""
Excel转JSON脚本 - 简化版本
"""

import pandas as pd
import json
import os
from datetime import datetime

# 配置文件路径
INPUT_FILE = r"D:\03Documents\WechatFiles\xwechat_files\wxid_cqn6qohvaxmv22_905a\msg\file\2025-08\副本提取(1).xlsx"

# 生成带时间戳的输出文件名
def generate_output_filename(input_file, sheet_name=None):
    base_name = os.path.splitext(os.path.basename(input_file))[0]
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    if sheet_name:
        return f"{base_name}_{sheet_name}_{timestamp}.json"
    else:
        return f"{base_name}_{timestamp}.json"

def process_sheet_to_json(df, sheet_name):
    """
    处理单个sheet的数据转换为JSON
    """
    # 确保至少有5列
    if len(df.columns) < 5:
        print(f"警告: Sheet '{sheet_name}' 列数不足5列，将补充空列")
        for i in range(len(df.columns), 5):
            df[f'col_{i}'] = ''
    
    # 设置列名
    df.columns = ['key', 'value', 'col3', 'col4', 'col5'] + list(df.columns[5:])
    
    print(f"Sheet '{sheet_name}': 读取到 {len(df)} 行数据")
    
    # 初始化结果字典
    result = {'get': {}, 'people': {}, 'compute': {}}
    
    # 处理数据
    for index, row in df.iterrows():
        key = row['key']
        value = row['value']
        
        # 跳过key为空的行
        if pd.isna(key) or str(key).strip() == '':
            continue
        
        key = str(key).strip()
        value = str(value).strip() if not pd.isna(value) else ''
        
        # 根据第3、4、5列是否为空分配到不同的JSON
        if not pd.isna(row['col3']) and str(row['col3']).strip() != '':
            result['get'][key] = value
        
        if not pd.isna(row['col4']) and str(row['col4']).strip() != '':
            result['people'][key] = value
        
        if not pd.isna(row['col5']) and str(row['col5']).strip() != '':
            result['compute'][key] = value
    
    return result

def excel_to_json(excel_file_path):
    """
    将Excel文件的所有sheet转换为JSON格式
    """
    try:
        # 获取所有sheet名称
        excel_file = pd.ExcelFile(excel_file_path)
        sheet_names = excel_file.sheet_names
        
        print(f"发现 {len(sheet_names)} 个Sheet: {sheet_names}")
        
        results = []
        
        for sheet_name in sheet_names:
            print(f"\n处理Sheet: {sheet_name}")
            
            # 读取当前sheet的前5列
            try:
                df = pd.read_excel(excel_file_path, sheet_name=sheet_name)
                
                # 处理当前sheet
                result = process_sheet_to_json(df, sheet_name)
                
                # 生成输出文件名
                output_file = generate_output_filename(excel_file_path, sheet_name)
                
                # 保存JSON文件
                with open(output_file, 'w', encoding='utf-8') as f:
                    json.dump(result, f, ensure_ascii=False, indent=2)
                
                print(f"转换完成: get({len(result['get'])}), people({len(result['people'])}), compute({len(result['compute'])})")
                print(f"已保存到: {output_file}")
                
                results.append({
                    'sheet_name': sheet_name,
                    'output_file': output_file,
                    'result': result
                })
                
            except Exception as e:
                print(f"处理Sheet '{sheet_name}' 失败: {e}")
                continue
        
        return results
        
    except Exception as e:
        print(f"转换失败: {e}")
        return None

def main():
    """主函数"""
    print("Excel转JSON转换工具 (多Sheet支持)")
    print("开始转换...")
    
    # 直接使用配置的文件路径
    results = excel_to_json(INPUT_FILE)
    
    if results:
        print(f"\n转换成功! 共处理了 {len(results)} 个Sheet")
        for result_info in results:
            print(f"- {result_info['sheet_name']}: {result_info['output_file']}")
    else:
        print("转换失败!")

if __name__ == "__main__":
    main()
